package dao.admins.impl;

import dao.BaseDao;
import dao.admins.IGoodsQuery;
import pojo.Goods;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class GoodsQueryImpl implements IGoodsQuery {
    private BaseDao baseDao = new BaseDao();
    @Override
    public List<Goods> queryAllGoods() {
        List<Goods> goods = new ArrayList<>();
        String sql = "SELECT goods.`id`, goods.goodsName, `smallclass`.smallName, goodsMoney, goodsNumber," +
                " goodsImage, goodsCarriage, goodsType, seller.`sellerName`, discount.`discRate`" +
                "FROM goods INNER JOIN seller ON goods.`goodsSeId` = seller.`id` " +
                "INNER JOIN `smallclass` ON goods.`goodsSmalId` = smallclass.`id` " +
                "INNER JOIN discount ON goods.`goodsDiscId` = discount.`id`";
        try (ResultSet rs = baseDao.executeSelect(sql, null)) {
            while (rs.next()) {
                Goods good = new Goods();
                setGoods(good, rs.getLong("id"), rs.getString("goodsName"), rs.getString("smallName"), rs.getDouble("goodsMoney"), rs.getInt("goodsNumber"),
                        rs.getString("goodsImage"), rs.getDouble("goodsCarriage"), rs.getInt("goodsType"), rs.getString("sellerName"), rs.getDouble("discRate"));
                goods.add(good);
            }
            baseDao.closeAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    @Override
    public int addGoods(Goods goods) {
        String sql = "insert into goods(goodsName,goodsSmalId,goodsMoney,goodsNumber,goodsImage,goodsCarriage,goodsType,goodsSeId,goodsDiscId) values(?,?,?,?,?,?,?,?,?)";
        Object[] params = {goods.getGoodsName(), goods.getGoodsSmalId(), goods.getGoodsMoney(), goods.getGoodsNumber(), goods.getGoodsImage(),
                goods.getGoodsCarriage(), goods.getGoodsType(), goods.getGoodsSeId(), goods.getGoodsDiscId()};
        int result = baseDao.executeUpdateRows(sql, params);
        baseDao.closeAll();
        return result;
    }

    @Override
    public Goods queryGoodsById(long id) {
        String sql ="SELECT * FROM goods where goods.id = ?";
        Goods goods = new Goods();
        Object[] params = {id};
        try (ResultSet rs = baseDao.executeSelect(sql, params)) {
            if (rs.next()) {
                setGoods(goods, rs.getLong("id"), rs.getString("goodsName"), rs.getLong("goodsSmalId"), rs.getDouble("goodsMoney"), rs.getInt("goodsNumber"),
                        rs.getString("goodsImage"), rs.getDouble("goodsCarriage"), rs.getInt("goodsType"), rs.getInt("goodsSeId"), rs.getInt("goodsDiscId"));
            }
            baseDao.closeAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return goods;
    }

    @Override
    public int updateGoods(Goods goods) {
        String sql = "update goods set goodsName = ? ,goodsSmalId = ?, goodsMoney = ?,goodsNumber = ?, " +
                "goodsCarriage = ?, goodsType = ?, goodsSeId = ?, goodsDiscId = ? where id = ?";
        Object[] params = {goods.getGoodsName(), goods.getGoodsSmalId(), goods.getGoodsMoney(), goods.getGoodsNumber(),
                goods.getGoodsCarriage(), goods.getGoodsType(), goods.getGoodsSeId(), goods.getGoodsDiscId(), goods.getId()};
        int result = baseDao.executeUpdateRows(sql, params);
        baseDao.closeAll();
        return result;
    }

    @Override
    public int deleteGoods(long id) {
        String sql = "delete from goods where id = ?";
        Object[] params = {id};
        int result = baseDao.executeUpdateRows(sql, params);
        baseDao.closeAll();
        return result;
    }

    public static final void setGoods(Goods goods, long id, String goodsName, long goodsSmalId, double goodsMoney, int goodsNumber,
                                       String goodsImage, double goodsCarriage, int goodsType, int goodsSeId, int goodsDiscId) {
        goods.setId(id);
        goods.setGoodsName(goodsName);
        goods.setGoodsSmalId(goodsSmalId);
        goods.setGoodsMoney(goodsMoney);
        goods.setGoodsNumber(goodsNumber);
        goods.setGoodsImage(goodsImage);
        goods.setGoodsCarriage(goodsCarriage);
        goods.setGoodsType(goodsType);
        goods.setGoodsSeId(goodsSeId);
        goods.setGoodsDiscId(goodsDiscId);
    }

    public static final void setGoods(Goods goods, long id, String goodsName, String smallName, double goodsMoney, int goodsNumber,
                                      String goodsImage, double goodsCarriage, int goodsType, String sellerName, double discount) {
        goods.setId(id);
        goods.setGoodsName(goodsName);
        goods.setSmallName(smallName);
        goods.setGoodsMoney(goodsMoney);
        goods.setGoodsNumber(goodsNumber);
        goods.setGoodsImage(goodsImage);
        goods.setGoodsCarriage(goodsCarriage);
        goods.setGoodsType(goodsType);
        goods.setSellerName(sellerName);
        goods.setDiscount(discount);
    }

    public Map<Integer, String> optionS(){
        String sql = "SELECT goodsSeId, sellerName FROM goods INNER JOIN seller ON goods.`goodsSeId` = seller.`id` GROUP BY seller.`id` ORDER BY goodsSeId;";
        Map<Integer, String> sellerName = new HashMap<>();
        try (ResultSet rs = baseDao.executeSelect(sql, null)){
            while (rs.next()){
                sellerName.put(rs.getInt("goodsSeId"), rs.getString("sellerName"));
            }
            baseDao.closeAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sellerName;
    }

    public Map<Integer, String> optionC(){
        String sql = "SELECT smallclass.`id`, smallclass.`smallName` FROM smallclass RIGHT JOIN goods ON smallclass.`id` = goods.`goodsSmalId` GROUP BY smallclass.`id` ORDER BY smallclass.`id`;";
        Map<Integer, String> sellerName = new HashMap<>();
        try (ResultSet rs = baseDao.executeSelect(sql, null)){
            while (rs.next()){
                sellerName.put(rs.getInt("id"), rs.getString("smallName"));
            }
            baseDao.closeAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sellerName;
    }

    public Map<Integer, Double> optionD(){
        String sql = "SELECT goodsDiscId, discRate FROM goods INNER JOIN discount ON goods.`goodsDiscId` = discount.`id`  GROUP BY discRate ORDER BY goodsDiscId;";
        Map<Integer, Double> discount = new HashMap<>();
        try (ResultSet rs = baseDao.executeSelect(sql, null)){
            while (rs.next()){
                discount.put(rs.getInt("goodsDiscId"), rs.getDouble("discRate"));
            }
            baseDao.closeAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return discount;
    }

}
